#!/user/bin/env python
# -*- coding: utf-8 -*-
from controls.data_topic_title_mysql import TopicTitleItem

from utils import log

from datetime import datetime, timedelta
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Table, MetaData, Column, Integer, String, Unicode, UnicodeText, DateTime, desc, TIMESTAMP, func
from controls.data_pool_mysql import engine

metadata = MetaData()
Session = sessionmaker()
Session.configure(bind=engine)

"""topic_note_item collection has
    id:             自增id（int）
    topicid:        标题id（int）
    noteid:         内容id（int）
    note:           内容（String）
    complete:       完成度（int），由客户端生成
    userid:         发布者id（int）
    tags:           标签（String）
    place:          地点标签（String）
    ctime:          讨论发布时间（datetime），由客户端生成
"""


class TopicNoteItem(object):
    """docstring for TopicNoteItem"""

    def to_dict(self):
        res = {}
        res['topicid'] = self.topicid
        res['noteid'] = self.noteid
        res['note'] = self.note
        res['complete'] = self.complete
        res['userid'] = self.userid
        res['tags'] = self.tags.strip(",").split(",") if len(self.tags) != 0 else []
        res['place'] = self.place.strip(",").split(",") if len(self.place) != 0 else []
        res['ctime'] = self.ctime.strftime("%Y-%m-%d %H:%M:%S")\
            if self.ctime else ""
        return res

    def __repr__(self):
        return "%d" % (self.topicid)


topic_note_table = Table(
    'topic_note', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('topicid', Integer, nullable=False),
    Column('noteid', Integer, index=True, unique=True, nullable=False),
    Column('note', UnicodeText, nullable=False),
    Column('complete', Integer, nullable=False),
    Column('userid', Integer, nullable=False),
    Column('tags', Unicode(60), nullable=False, default=''),
    Column('place', Unicode(60), nullable=False, default=''),
    Column('ctime', TIMESTAMP, server_default=func.now()),
)

mapper(TopicNoteItem, topic_note_table)


def create_topic_note_table_if_not_exist():
    metadata.create_all(engine)


def add_topic_note_item(p_item):
    item = TopicNoteItem()
    item.topicid = p_item['topicid']
    item.noteid = p_item['noteid']
    item.note = p_item['note']
    item.complete = p_item['complete']
    item.userid = p_item['userid']
    item.tags = p_item['tags']
    item.place = p_item['place']

    try:
        session = Session()
        session.add(item)
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


def get_topic_note_page():
    try:
        session = Session()
        items = session.query(TopicNoteItem) \
            .order_by(desc(TopicNoteItem.ctime)).offset(0).limit(20).all()
        res_list = [item.to_dict() for item in items]
        return res_list
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def get_topic_note_by_topicid(p_topicid, p_begin, p_limit, except_list=[]):
    try:
        session = Session()
        note_query = session.query(TopicNoteItem).filter_by(topicid=p_topicid)
        if except_list:
            note_query = note_query.filter(~TopicNoteItem.noteid.in_(except_list))
        items = note_query.order_by(desc(TopicNoteItem.ctime)) \
            .offset(p_begin).limit(p_limit).all()
        res_list = [item.to_dict() for item in items]
        return res_list
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


'''
def get_topic_note_by_and_topicid(p_topicid):
    try:
        session = Session()
        r_item = session.query(TopicNoteItem).filter_by(topicid=p_topicid) \
            .order_by(desc(TopicNoteItem.ctime)) \
            .offset(0).limit(20).all()
        res = r_item.to_dict() if r_item else None
        return res
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_note_by_noteidlist(note_id_list):
    try:
        session = Session()
        r_item = session.query(TopicNoteItem) \
            .filter(TopicNoteItem.userid.in_(note_id_list)) \
            .order_by(desc(TopicNoteItem.ctime)) \
            .offset(0).limit(20).all()
        res = r_item.to_dict()

        return res
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()
'''
def get_topic_note_count_by_topicid_list(p_topicid_list):
    try:
        session = Session()
        items = session.query(TopicNoteItem.topicid,func.count(TopicNoteItem)) \
            .filter(TopicNoteItem.topicid.in_(p_topicid_list)) \
            .group_by(TopicNoteItem.topicid).all()
        topic_note_count_map = dict(items)
        return topic_note_count_map
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_note_by_noteid(p_noteid):
    try:
        session = Session()
        item = session.query(TopicNoteItem).filter_by(noteid=p_noteid).first()
        res = item.to_dict()
        return res
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def delete_topic_note_by_noteid(p_noteid):
    try:
        session = Session()
        session.query(TopicNoteItem).filter_by(noteid=p_noteid).delete()
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


CURRENT_HOT_TIME = 3600 * 24
def get_hot_topic_id_by_provid(p_provid, offset, except_list):
    try:
        session = Session()
        item_query = session.query(TopicNoteItem.topicid,
                                   func.count(TopicNoteItem).label('count')) \
            .join(TopicTitleItem, TopicNoteItem.topicid == TopicTitleItem.topicid) \
            .filter(TopicTitleItem.provid == p_provid) \

        if except_list:
            item_query = item_query.filter(~TopicTitleItem.topicid.in_(except_list))

        item = item_query.filter(
            TopicNoteItem.ctime >
            (datetime.now() - timedelta(seconds=CURRENT_HOT_TIME))
        ).group_by(TopicNoteItem.topicid)\
            .order_by('count desc').limit(1).offset(offset).first()
        if item:
            log.i(item)
            return item[0]
        else:
            return None
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


# def get_topic_dict_by_noteid_list(p_noteid_list):
#     try:
#         session = Session()
#         subquery = session.query(TopicNoteItem.topicid)\
#             .filter(TopicNoteItem.noteid.in_(p_noteid_list)).subquery()
#         items = session.query(TopicNoteItem.noteid,
#                               TopicTitleItem) \
#             .filter(TopicTitleItem.topicid.in_(subquery)).all()
#
#         return {t[0]: t[1].to_dict() for t in items}
#     except Exception as e:
#         log.exp(e)
#         session.rollback()
#         return None
#     finally:
#         session.close()


def get_note_topic_map_by_noteid_list(p_noteid_list):
    try:
        session = Session()
        items = session.query(TopicNoteItem.noteid, TopicNoteItem.topicid)\
            .filter(TopicNoteItem.noteid.in_(p_noteid_list)).order_by(desc(TopicNoteItem.ctime)).all()

        return dict(items)
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_note_by_userid(p_userid):
    try:
        session = Session()
        items = session.query(TopicNoteItem)\
            .filter(TopicNoteItem.userid == p_userid).all()

        return [i.to_dict() for i in items]
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def get_topic_note_by_userid_list(p_userid_list, p_begin, p_limit):
    try:
        session = Session()
        items = session.query(TopicNoteItem).filter(TopicNoteItem.userid.in_(p_userid_list)) \
            .order_by(TopicNoteItem.ctime.desc()).offset(p_begin).limit(p_limit).all()
        result = [item.to_dict() for item in items]
        return result

    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_note_map_by_noteid_list(p_noteid_list):
    try:
        session = Session()
        items = session.query(TopicNoteItem)\
            .filter(TopicNoteItem.noteid.in_(p_noteid_list)).all()

        return {i.noteid: i.to_dict() for i in items}
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_all_topic_note():
    """
    在刷新脚本里使用，正式环境中尽量不要使用
    慎用！！！
    """
    try:
        session = Session()
        items = session.query(TopicNoteItem).all()
        result = [item.to_dict() for item in items]
        return result
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def update_topic_note_by_noteid(p_noteid, p_note_item):
    try:
        session = Session()
        session.query(TopicNoteItem).filter_by(noteid=p_noteid).update(p_note_item)
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


if __name__ == "__main__":
    create_topic_note_table_if_not_exist()
    item = {'topicid': 1,
            'noteid': 1,
            'note': 'notetest',
            'complete': 10,
            'userid': 1,
            'tags': 'tagtest',
            'place': 'tagtest',
            'ctime': datetime.now()
            }
    add_topic_note_item(item)
